using Ocean.Transfer.Tools.Data;
using System.Data;
using System.Drawing;
using System.Windows.Forms;

namespace Ocean.Transfer.Tools
{
    public partial class FrmSqliteToSQLServer : Form
    {
        private bool db_copy_running = false;

        private readonly List<string> sqlserver_tables = []; 
        public FrmSqliteToSQLServer()
        {
            InitializeComponent();

            #region 表
            dgv_table.AllowUserToAddRows = false;
            dgv_table.AllowUserToDeleteRows = false;
            dgv_table.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            dgv_table.MultiSelect = false;
            dgv_table.RowHeadersWidth = 45;
            dgv_table.AutoGenerateColumns = false;
            dgv_table.EditMode = DataGridViewEditMode.EditOnEnter;

            DataGridViewCheckBoxColumn cbc = new()
            {
                Name = "cb",
                DataPropertyName = "cb",
                HeaderText = "",
                ValueType = typeof(bool),
                Width = 40,
                Visible = true,
                ReadOnly = false,
                SortMode = DataGridViewColumnSortMode.NotSortable
            };
            dgv_table.Columns.Add(cbc);

            DataGridViewTextBoxColumn textc = new()
            {
                Name = "sqlite",
                DataPropertyName = "sqlite",
                HeaderText = "Sqlite",
                Width = 200,
                Visible = true,
                ReadOnly = true,
                SortMode = DataGridViewColumnSortMode.NotSortable
            };
            dgv_table.Columns.Add(textc);

            DataGridViewComboBoxColumn totb = new()
            {
                Name = "sqlserver",
                DataPropertyName = "sqlserver",
                HeaderText = "SQL Server",
                Width = 200,
                Visible = true,
                SortMode = DataGridViewColumnSortMode.NotSortable,
                DisplayStyle = DataGridViewComboBoxDisplayStyle.DropDownButton // 确保显示样式正确
            };

            SqlServerHelper conn2 = new();
            sqlserver_tables = CopyTable.GetAllSqlServerTables(conn2);

            totb.DisplayMember = "name";
            totb.ValueMember = "id";
            totb.DataSource = new[] { new { id = "", name = "请选择" } }
                .Concat(sqlserver_tables.Select(x => new { id = "dbo." + x, name = "dbo." + x }))
                .ToList();

            dgv_table.Columns.Add(totb);


            cbc = new()
            {
                Name = "run",
                DataPropertyName = "run",
                HeaderText = "已执行",
                ValueType = typeof(bool),
                Width = 60,
                Visible = true,
                ReadOnly = true,
                SortMode = DataGridViewColumnSortMode.NotSortable
            };
            dgv_table.Columns.Add(cbc);

            textc = new()
            {
                Name = "error",
                DataPropertyName = "error",
                HeaderText = "Error",
                Width = 150,
                Visible = true,
                ReadOnly = true,
                SortMode = DataGridViewColumnSortMode.NotSortable
            };
            dgv_table.Columns.Add(textc);
            dgv_table.RowPostPaint += Dgv_table_RowPostPaint;
            dgv_table.RowEnter += Dgv_table_RowEnter;
            #endregion

            progressBar_jindu.Visible = false;

            ck_select_all.Enabled = false;
            sbt_transdata.Enabled = false;
            sbt_createtable.Enabled = false;

            ck_select_all.Click += Cb_selectAll_Click;
            sbt_transdata.Click += Sbt_transdata_Click;
            sbt_createtable.Click += Sbt_createtable_Click;

            btn_sql.Click += (s, e) =>
            {
                rtb_OutInfo.Text = "update [dbo].[sys_tb] set tb_name = 'dbo.' + tb_name where tb_type = 1 and tb_name not like 'dbo.%';";
            };

            Load += Form1_Load;
        }


        private void Form1_Load(object? sender, EventArgs e)
        {
            SQLiteHelper conn = new();
            List<string> tables = CopyTable.GetAllSQLiteTables(conn);
            dgv_table.DataSource = tables.Select(x => new
            {
                sqlite = x, 
            }
            ).ToList();

            foreach (DataGridViewRow row in dgv_table.Rows)
            {
                if (row.Cells["sqlite"].Value != null && sqlserver_tables.Contains(row.Cells["sqlite"].Value?.ToString() ?? string.Empty))
                {
                    row.Cells["sqlserver"].Value = "dbo." + (row.Cells["sqlite"].Value?.ToString() ?? string.Empty);
                }
            }

            ck_select_all.Enabled = true;
            sbt_transdata.Enabled = true;
            sbt_createtable.Enabled = true;
        }

        private void Dgv_table_RowPostPaint(object? sender, DataGridViewRowPostPaintEventArgs e)
        {
            var grid = sender as DataGridView;
            var rowIdx = (e.RowIndex + 1).ToString();

            var centerFormat = new StringFormat()
            {
                // right alignment might actually make more sense for numbers
                Alignment = StringAlignment.Center,
                LineAlignment = StringAlignment.Center
            };

            var headerBounds = new Rectangle(e.RowBounds.Left, e.RowBounds.Top, grid.RowHeadersWidth, e.RowBounds.Height);
            var brush = SystemBrushes.ControlText;
            if (brush != null)
            {
                e.Graphics.DrawString(rowIdx, this.Font ?? SystemFonts.DefaultFont, brush, headerBounds, centerFormat);
            }
        }

        private void Dgv_table_RowEnter(object? sender, DataGridViewCellEventArgs e)
        {
            if (dgv_table.SelectedCells.Count == 0)
            {
                rtb_OutInfo.Text = "";
            }
            else
            {
                DataGridViewRow row = dgv_table.Rows[dgv_table.SelectedCells[0].RowIndex];
                rtb_OutInfo.Text = row.Cells["error"].Value?.ToString() ?? "";
            }
        }

        private void Cb_selectAll_Click(object? sender, EventArgs e)
        {
            var tf = ck_select_all.Checked;
            foreach (DataGridViewRow row in dgv_table.Rows)
            {
                if (row.Cells["cb"] != null)
                {
                    row.Cells["cb"].Value = tf;
                }
            }
        }

        private void Sbt_transdata_Click(object? sender, EventArgs e)
        {
            if (db_copy_running)
            {
                MessageBox.Show("正在执行中，不能重复执行...");
                return;
            }

            int select = 0;
            foreach (DataGridViewRow row in dgv_table.Rows)
            {
                if (row.Cells["cb"].Value != null && row.Cells["cb"].Value is bool cbValue && cbValue)
                {
                    select++;
                }
            }
            if (select == 0)
            {
                return;
            }

            if (MessageBox.Show("请确认数据库是否已备份，确定更新数据...", "更新数据?", MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.No)
                return;

            Thread t = new(() => this.Invoke(new Action(() =>
            {
                try
                {
                    DbCopyRun();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("执行错误，" + ex.Message);
                }
            })));
            t.Start();
        }

        private void DbCopyRun()
        {
            db_copy_running = true;
            try
            {
                SQLiteHelper conn = new();
                SqlServerHelper conn2 = new();

                progressBar_jindu.Maximum = dgv_table.Rows.Count;
                progressBar_jindu.Value = 0;
                progressBar_jindu.Visible = true;

                foreach (DataGridViewRow row in dgv_table.Rows)
                {
                    if (!db_copy_running)
                    {
                        break;
                    }

                    if (row.Cells["cb"].Value != null && row.Cells["cb"].Value is bool cbValue && cbValue &&
                        (row.Cells["run"].Value == null || (row.Cells["run"].Value is bool runValue && !runValue)))
                    {
                        try
                        {
                            string sqlite_tbname = row.Cells["sqlite"].Value?.ToString() ?? string.Empty;
                            string sqlserver_tbname = row.Cells["sqlserver"].Value?.ToString() ?? string.Empty;
                            if (!string.IsNullOrEmpty(sqlserver_tbname))
                            {
                                CopyTable.FromSqliteToSqlServer(conn, conn2, sqlite_tbname, sqlserver_tbname);
                                row.Cells["run"].Value = true;
                            }
                        }
                        catch (Exception ex)
                        {
                            row.Cells["run"].Value = false;
                            row.Cells["error"].Value = ex.Message;
                        }
                    }

                    progressBar_jindu.Value++;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("执行错误，" + ex.Message);
            }
            finally
            {
                db_copy_running = false;
                progressBar_jindu.Visible = false;
            }
        }

        private void Sbt_createtable_Click(object? sender, EventArgs e)
        {
            if (db_copy_running)
            {
                MessageBox.Show("正在执行中，不能重复执行...");
                return;
            }

            int select = 0;
            foreach (DataGridViewRow row in dgv_table.Rows)
            {
                if (row.Cells["cb"].Value != null && row.Cells["cb"].Value is bool cbValue && cbValue)
                {
                    select++;
                }
            }
            if (select == 0)
            {
                return;
            }

            if (MessageBox.Show($"请确认数据库是否已备份，确定创建 {select} 个表结构...", "创建表结构?", MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.No)
                return;

            Thread t = new(() => this.Invoke(new Action(() =>
            {
                try
                {
                    DbCreateTableRun();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("执行错误，" + ex.Message);
                }
            })));
            t.Start();
        }

        private void DbCreateTableRun()
        {
            db_copy_running = true;
            try
            {
                SQLiteHelper conn = new();
                SqlServerHelper conn2 = new();

                progressBar_jindu.Maximum = dgv_table.Rows.Count;
                progressBar_jindu.Value = 0;
                progressBar_jindu.Visible = true;

                foreach (DataGridViewRow row in dgv_table.Rows)
                {
                    if (!db_copy_running)
                    {
                        break;
                    }

                    if (row.Cells["cb"].Value != null && row.Cells["cb"].Value is bool cbValue && cbValue &&
                        (row.Cells["run"].Value == null || (row.Cells["run"].Value is bool runValue && !runValue)))
                    {
                        try
                        {
                            string sqlite_tbname = row.Cells["sqlite"].Value?.ToString() ?? string.Empty;
                            string sqlserver_tbname = row.Cells["sqlserver"].Value?.ToString() ?? string.Empty;
                            if (!string.IsNullOrEmpty(sqlserver_tbname))
                            {
                                CopyTable.CreateTableFromSQLite(conn, conn2, sqlite_tbname, sqlserver_tbname);
                                row.Cells["run"].Value = true;
                            }
                        }
                        catch (Exception ex)
                        {
                            row.Cells["run"].Value = false;
                            row.Cells["error"].Value = ex.Message;
                        }
                    }

                    progressBar_jindu.Value++;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("执行错误，" + ex.Message);
            }
            finally
            {
                db_copy_running = false;
                progressBar_jindu.Visible = false;
            }
        }
    }
}